CREATE TABLE source_files (
	id NUMBER(38) NOT NULL,
    path_name VARCHAR2(4000) NOT NULL,
    file_name VARCHAR2(255) NOT NULL,
    file_type VARCHAR2 (10) NOT  NULL,
    added_time DATE DEFAULT SYSDATE,
    is_loaded NUMBER(1) DEFAULT 0,
    loaded_time DATE
);

--------------------------------------------------------------------------------
ALTER TABLE source_files
	ADD CONSTRAINT source_files_pk PRIMARY KEY (path_name, file_name);

ALTER TABLE source_files
	ADD CONSTRAINT id_unique UNIQUE (id);

CREATE SEQUENCE sf_id_seq START WITH 1 INCREMENT BY 1 CACHE 100;

-- Types

CREATE OR REPLACE TYPE sf_return_type IS OBJECT (id NUMBER(38),
												 path_name VARCHAR2(4000),
												 file_name VARCHAR2(255));

CREATE OR REPLACE TYPE sf_return_tbl IS TABLE OF sf_return_type;

--------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE load_data
    AUTHID CURRENT_USER
IS
    PROCEDURE SF_ADD(path_name_in IN source_files.path_name%TYPE, 
                                     file_name_in IN source_files.file_name%TYPE,
                                     file_type_in IN source_files.file_type%TYPE);
	FUNCTION SF_GET_FILES_FOR_LOAD (file_type_in IN source_files.file_type%TYPE, 
									file_date IN DATE := NULL)
			RETURN sf_return_tbl
			PIPELINED
			PARALLEL_ENABLE;
END load_data;

CREATE OR REPLACE PACKAGE BODY load_data
IS
    PROCEDURE SF_ADD(path_name_in IN source_files.path_name%TYPE, 
                                       file_name_in IN source_files.file_name%TYPE,
                                       file_type_in IN source_files.file_type%TYPE)
    IS
		row_exist NUMBER(1);
		CURSOR check_cur
		IS
			SELECT 1 FROM source_files sf
	            WHERE path_name_in = sf.path_name
                AND file_name_in = sf.file_name;
    BEGIN
        OPEN check_cur;
        LOOP
	        FETCH check_cur INTO row_exist;
    	    EXIT WHEN check_cur%NOTFOUND;
    	END LOOP;
    	CLOSE check_cur;
        IF row_exist IS NULL
        	THEN
	            INSERT INTO source_files sf (sf.id, sf.path_name, sf.file_name, sf.file_type)
    	        VALUES (sf_id_seq.NEXTVAL, path_name_in, file_name_in, file_type_in);
    	        COMMIT;
    	END IF;
        	
    EXCEPTION
        WHEN OTHERS THEN
        	DBMS_OUTPUT.put_line(SQLCODE || ': ' || SQLERRM);
    END SF_ADD;

	FUNCTION SF_GET_FILES_FOR_LOAD (file_type_in IN source_files.file_type%TYPE, file_date IN DATE := NULL)
	RETURN sf_return_tbl
	PIPELINED
	PARALLEL_ENABLE
	IS
		TYPE return_data_cur IS REF CURSOR;
		data_cur return_data_cur;
		ret sf_return_type := sf_return_type(NULL, NULL, NULL);
		pn VARCHAR2(4000);
		fn VARCHAR2(255);
	BEGIN
		IF file_date IS NULL
		THEN
			OPEN data_cur FOR 
				SELECT sf_return_type(sf.id, sf.path_name, sf.file_name)
				FROM source_files sf
				WHERE sf.file_type = file_type_in
						AND sf.is_loaded = 0;
			LOOP
			   FETCH data_cur INTO ret;
		       EXIT WHEN data_cur%NOTFOUND;
		       PIPE ROW(ret);
		    END LOOP;
		    CLOSE data_cur;
		ELSE
			OPEN data_cur FOR 
				SELECT sf_return_type(sf.id, sf.path_name, sf.file_name)
				FROM source_files sf
				WHERE sf.file_type = file_type_in
						AND sf.is_loaded = 0
						AND sf.file_name LIKE '%'||TO_CHAR(file_date,'YYYY-MM-DD')||'%';
			LOOP
			   FETCH data_cur INTO ret;
		       EXIT WHEN data_cur%NOTFOUND;
		       PIPE ROW(ret);
		    END LOOP;
		    CLOSE data_cur;
		END IF;
	    RETURN;
	END SF_GET_FILES_FOR_LOAD;

END load_data;
